# coding=utf-8
__author__ = 'Administrator'
#jk409 update 2015-5-23
import pymysql
#import psycopg2
#import pymongo
import sqlite3
import redis
class Mysql:
    def __init__(self,host,user,password,db,port):
        self.host = host
        self.user = user
        self.passwd = password
        self.db = db
        self.port = port
        self.charset = 'utf8'

    def read(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,port=self.port,user=self.user, passwd=self.passwd, db=self.db,charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            data = cur.fetchall()
            #字典json格式的
            #data_dict = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in self.cur.fetchall()]
            #data_dict = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
            #print('\n',data_dict)
        except:
            print('数据查询失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return data


    def write(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,user=self.user, passwd=self.passwd, db=self.db, charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            cnn.commit()
        except:
            cnn.rollback()
            print('数据写入失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return True

class Mongodb():
    def __init__(self, ip, port, db):
        self.conn = pymongo.Connection(ip, port)
        self.db = self.conn[db]

    def find(self,table):
        return self.db[table].find({}).limit(1500)

    def find_one(self,table,xarg):
        return self.db[table].find_one(xarg)

    def insert(self,table, xarg):
        try:
            data = self.db[table]
            data.insert(xarg)
        finally:
            self.conn.close()

    def remove(self,table ,xarg):
        try:
            data = self.db[table]
            data.remove(xarg)
        finally:
            self.conn.close()

    def save(self,table, xarg):
        try:
            data = self.db[table]
            data.save(xarg)
        finally:
            self.conn.close()

    def update(self,table ,*xarg):
        try:
            data = self.db[table]
            data.update(*xarg)
        finally:
            self.conn.close()

class redis():
    def __init__(self,host,port,db,passwd):
        #启用Pool
        #self.pools=redis.ConnectionPool(host='localhost',port=6379,db=0,password='',encoding='utf-8')
        #self.cnn=redis.Redis(connection_pool=self.pools)
        self.cnn=redis.Redis(host=host,port=port,db=db,password=passwd,encoding='utf-8')

    def get(self,name):
        data = self.cnn
        data.get(name)

    def append(self,key,value):
        data = self.cnn
        data.append(key,value)

class Sqlite3():
    def __init__(self,db):
        '''
        :param db:
        ：python插入的数据正常
        :客户端工具添加的数据会有编码的问题，查询返回结果的
        字段有汉子的话需要使用gbk读
        字段是数字字母字符串默认读取即可（如下：）
        :utf8 res[0]['tag']
        :gbk res[0]['tag'].decode('gb2312').encode('utf-8')
        '''
        self.db=db

    def read(self,sqls,args=()):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!'%self.db)
            return False
        try:
            self.cur = self.cnn.cursor()
            data=self.cur.execute(sqls,args).fetchall()
            res = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
        except:
            res= False
            print('数据库读取失败!')
        finally:
            self.cnn.close()
        return  res

    def find(self, args=()):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!' % self.db)
            return False

        try:
            self.cur = self.cnn.cursor()
            data = self.cur.execute(args).fetchone()
            res = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
        except:
            res=False
            print('数据库读取失败，条件：%s' % args)
        finally:
            self.cnn.close()
        return res

    def write(self, sqls, args=()):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!' % self.db)
            return False

        try:
            #lastrowid:返回ID
            #res=self.cnn.cursor().execute(sqls,args).lastrowid
            self.cnn.cursor().execute(sqls, args)
            self.cnn.commit()
            self.cnn.close()
            return True
        except:
            self.cnn.rollback()
            self.cnn.close()
            print('写入%s数据库失败!' % self.db)
            return False



    #初始化
    def INIT(self):
        print('开始初始化数据库，数据将会清空！')
        self.write('drop table domain','')
        self.write('drop table user', '')
        domain_sql = '''
        create table domain(
            id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            domain varchar(32) NOT NULL,
            ip varchar(15) NOT NULL,
            status int(1),
            create_time int(10),
            update_time int(10)
        );
        '''
        user_sql = '''
            create table user(
                id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                name varchar(16) NOT NULL,
                email varchar(24) UNIQUE NOT NULL,
                passwd varchar(32) NOT NULL,
                status int(1),
                is_active int(1),
                qx int(1),
                create_time int(11),
                update_time int(11)
            )
            '''
        add_domain_data='insert into domain (`domain`,`ip`,`status`,`create_time`) values (?,?,?,?);'
        add_user_data = 'insert into user (`name`,`email`,`passwd`,`status`,`is_active`,`qx`,`create_time`) values (?,?,?,?,?,?,?);'
        print('开始初始化domain表......')
        self.write(domain_sql,'')
        print('开始初始化domain表数据......')
        self.write(add_domain_data, ('test.kkk.com', '192.168.1.9', '0', int(time.time())))

        print('开始初始化user表......')
        self.write(user_sql, '')
        print('开始初始化User表数据......')
        self.write(add_user_data, ('jk409','jk409@qq.com','123456',0,1,1,int(time.time())))

class Postgrep():
    def __init__(self, host, user, password, db, port=5433):
        self.host = host
        self.user = user
        self.passwd = password
        self.db = db
        self.port = port
        #self.charset = 'utf8'

    def read(self, sql, args=()):
        try:
            cnn = psycopg2.connect(host=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db)
            cur = cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql, args)
            data = cur.fetchall()
            # 字典json格式的
            # data_dict = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in self.cur.fetchall()]
            data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
            # print('\n',data_dict)
        except:
            print('数据查询失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return data

    def write(self, sql, args=()):
        try:
            cnn=psycopg2.connect(host=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db)
            # self.cnn.autocommit(True)
            cur = cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            print '----'
            cur.execute(sql, args)
            cnn.commit()
        except:
            cnn.rollback()
            print('数据写入失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return True

    def INIT(self):
        sqls='''
            CREATE TABLE public."user"
(
  id serial NOT NULL,
  name character varying(32),
  age integer,
  phone   character varying(12),
  address character varying(64)
  )
WITH (
  OIDS=FALSE
);
ALTER TABLE public."user"
  OWNER TO postgres;
        '''
        add_user='insert into public.user (name,age,phone,address) values (%s,%s,%s,%s)'
        self.write(sqls)
        self.write(add_user,('kkk',28,'13071322539','深圳-南山'))

if __name__ == '__main__':
    #sqlite3
    #sqls = 'insert into kkk (`name`,`age`,`address`) values (?,?,?);'
    #res = db.write(sqls, ('kkk', '29', '广东-深圳', 'ss'))
    #s不太安全的sql写法
    #sqls='''select * from user where name like '%s' order by %s limit %s;'''
    #orders = 'id desc'
    #limits="0,2"
    #strs='test'+"%"
    #sqls2=sqls%(strs,orders,limits)
    #print sqls2
    #mysql=Mysql('127.0.0.1','root','123456','kkk').read(sqls%(strs,orders,limits))
    #print mysql
    #--------------------------------------------------------------------------------
    # 第2种方式
    #sqls = '''select * from user where name like %s  order by %s desc limit %s,%s;'''
    #orders = "id desc"
    #limits = "0,2"
    #wheres="name like 'test%' "
    #strs = 'test' + "%"
    #sqls2 = sqls % (strs, orders, limits)
    #print sqls2
    #mysql = Mysql('127.0.0.1', 'root', '123456', 'kkk').read(sqls,(strs,'`id`',0,3))
    #print mysql
    #--------------------------------------------------------------------------------
    # #第3种方式（推荐）
    # wherestr = "WHERE 1=1 "
    # wherestr = ' '.join((wherestr, "AND name like %s"))
    # wherestr = ' '.join((wherestr, "OR email like %s"))
    # wherestr = ' '.join((wherestr, 'AND status=%d' % (1,)))
    # wherestr = ' '.join((wherestr, 'Order by %s' % ('id desc')))
    # wherestr = ' '.join((wherestr, 'Limit %d,%d' % (0,2)))
    #
    # sqls = '''select * from user {where};'''.format(where=wherestr)
    # print sqls
    # strs='test'+"%"
    # mysql = Mysql('127.0.0.1', 'root', '123456', 'kkk').read(sqls,(strs,strs,))
    # print mysql
    # pass
    # db=Sqlite3('./test.db')
    # sqls = '''
    #     create table kkk(
    #         id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    #         name varchar(16) NOT NULL,
    #         age int(3),
    #         zw  varchar(15) NOT NULL,
    #         address varchar(64) NOT NULL
    #     );
    #     '''
    # #db.write(sqls,'')
    # for i in range(1008,10000):
    #     sqls = 'insert into kkk (`name`,`age`,`zw`,`address`) values (?,?,?,?);'
    #     res = db.write(sqls, ('kkk', '29','python开发','广东-深圳-同方信息港A栋10楼'))
    #     #print i
    pass
